﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LumenWorks.Framework.IO.Csv;
using System.Data.SQLite;

namespace estiglp1112_5479_csharp_rec
{
    /**
     * Data: 23/02/2012
     * Autor: João Vitor Machado Batista
     */
    class csv2db
    {

        const string dataSource = "Data Source = ..\\..\\database\\rebides.db";

        /**
         * Cria e preenche a base de dados
         */
        public void createDataBase()
        {
            SQLiteConnection conn = new SQLiteConnection(dataSource);
            conn.Open();
            SQLiteTransaction trans = conn.BeginTransaction();
            SQLiteCommand cmd = conn.CreateCommand();

            for (int x = 0; x < 11; x++)
            {
                int year = 2000 + x;
                string drop = string.Format("DROP TABLE IF EXISTS data_{0}", year);

                cmd.CommandText = drop;
                cmd.ExecuteNonQuery();

                string create = string.Format("CREATE TABLE IF NOT EXISTS data_{0} (prof_ID integer, prof_name text, prof_last_degree text, prof_course text, prof_category text, prof_regime text, year integer, establishment_type text,establishment_name text)", year);

                cmd.CommandText = create;
                cmd.ExecuteNonQuery();

                string csvFile = string.Format("..\\..\\rebcsv\\reb{0}.csv", x);
                CsvReader readFile = new CsvReader( new StreamReader(csvFile), true);

                while (readFile.ReadNextRecord())
                {
                    string csv0 = readFile[0].Replace('\'', ' ');
                    string csv1 = readFile[1].Replace('\'', ' ');
                    string csv2 = readFile[2].Replace('\'', ' ');
                    string csv3 = readFile[3].Replace('\'', ' ');
                    string csv4 = readFile[4].Replace('\'', ' ');
                    string csv5 = readFile[5].Replace('\'', ' ');
                    string csv6 = readFile[6].Replace('\'', ' ');
                    string csv7 = readFile[7].Replace('\'', ' ');
                    string csv8 = readFile[8].Replace('\'', ' ');

                    string insert = string.Format("INSERT INTO data_{0} (prof_ID, prof_name, prof_last_degree, prof_course, prof_category, prof_regime, year, establishment_type, establishment_name) values('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')", year, csv0, csv1, csv2, csv3, csv4, csv5, csv6, csv7, csv8);

                    cmd.CommandText = insert;
                    cmd.ExecuteNonQuery();
                }
            }
            
            trans.Commit();
            conn.Close();
        }

    }

}
